﻿using blog.Dao;
using blog.Model;
using Microsoft.AspNetCore.Mvc;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using static Microsoft.Extensions.Logging.EventSource.LoggingEventSource;

namespace blog.Controllers
{
    [Route("api/blog/[action]")]
    [ApiController]
    public class ContentController : Controller
    {
        [HttpGet]
        public string GetDateTime()
        {
            return DateTime.Now.ToString();
        }

        [HttpPost]
        public Result<int> AddBlog([FromBody]Content content)
        {
            Result<int> res = new Result<int>();
            int count = 0;
            string sql = "insert into content(userId,title,contentMd,contentHtml,abs,cover,createTime) values(@userId,@title,@contentMd,@contentHtml,@abs,@cover,@createTime)";
            SqlParameter[] parameters =
            {
                new SqlParameter("@userId",content.UserId),
                new SqlParameter("@title",content.Title),
                new SqlParameter("@contentMd",content.ContentMd),
                new SqlParameter("@contentHtml",content.ContentHtml),
                new SqlParameter("@abs",content.Abs),
                new SqlParameter("@cover",content.Cover),
                //new SqlParameter("@createTime",content.CreateTime),
                new SqlParameter("@createTime",DateTime.Now),
            };
            count = DBHelper.ExecuteNonQuery(sql, parameters);
            res.Message = "新增成功";
            res.Code = 200;
            res.Data = count;
            return res;
        }

        [HttpDelete]
        public Result<int> DeleteBlog(int id)
        {
            Result<int> res = new Result<int>();
            int count = 0;
            string sql = "delete from content where id=@id";
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };

            count = DBHelper.ExecuteNonQuery(sql, parameters);
            res.Message = "删除成功";
            res.Code = 200;
            res.Data = count;
            return res;
        }

        [HttpPost]
        public Result<int> UpdateBlog(Content content)
        {
            Result<int> res = new Result<int>();
            int count = 0;
            string sql = "update content set title=@title,contentMd=@contentMd,contentHtml=@contentHtml,abs=@abs,cover=@cover where id=@id";
            SqlParameter[] parameters =
            {
                new SqlParameter("@title",content.Title),
                new SqlParameter("@contentMd",content.ContentMd),
                new SqlParameter("@contentHtml",content.ContentHtml),
                new SqlParameter("@abs",content.Abs),
                new SqlParameter("@cover",content.Cover),
                new SqlParameter("@id",content.Id),
            };
            count = DBHelper.ExecuteNonQuery(sql, parameters);
            res.Message = "修改成功";
            res.Code = 200;
            res.Data = (int)content.Id;
            return res;
        }

        [HttpGet]
        public Result<Page> SelectBlogByUserId(int userId)
        {
            Result<Page> res = new Result<Page>();
            List<Content> contents= new List<Content>();
            string sql = "select * from content where userId=@userId";
            SqlParameter[] parameters =
            {
                new SqlParameter("@userId",userId),
            };
            //2、调用DataSet通用的查询的方法
            //DataSet ds=DBHelper.GetDataSet(sql,null);
            DataSet ds = DBHelper.GetDataSet(sql, parameters);

            //4、获得DataSet对象中指定的表（学生表）
            DataTable dt = ds.Tables[0];

            //5、遍历表中的每一行，把每行中的各个字段的值依次存入学生对象，添加学生对象到学生列表
            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            Page page = new Page();
            page.Contents=contents;
            int total = 0;
            total = CountContentByUserId(userId);
            page.Total = total;
            res.Data=page;
            return res;
        }

        [HttpGet]
        public Result<Page> SearchBlog(string keyword)
        {
            Result<Page> res = new Result<Page>();
            List<Content> contents = new List<Content>();
            string sql = "select content.* from content inner join users on content.userId=users.id where contentMd like @keyword or title like @keyword or users.username like @keyword";
            SqlParameter[] parameters =
            {
                new SqlParameter("@keyword","%"+keyword+"%"),
            };
            DataSet ds = DBHelper.GetDataSet(sql, parameters);
            DataTable dt = ds.Tables[0];
            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            Page page = new Page();
            page.Contents = contents;
            int total = 0;
            total = CountContentByKeyword(keyword);
            page.Total = total;
            res.Data = page;
            return res;
        }

        [HttpGet]
        public Result<Page> SearchSelfBlog(string keyword,int userId)
        {
            Result<Page> res = new Result<Page>();
            List<Content> contents = new List<Content>();
            string sql = "select * from content where contentMd like @keyword or title like @keyword and userId=@userId";
            SqlParameter[] parameters =
            {
                new SqlParameter("@keyword","%"+keyword+"%"),
                 new SqlParameter("@userId",userId),
            };
            DataSet ds = DBHelper.GetDataSet(sql, parameters);
            DataTable dt = ds.Tables[0];
            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            Page page = new Page();
            page.Contents = contents;
            int total = 0;
            total = CountContentByKeyword(keyword);
            page.Total = total;
            res.Data = page;
            return res;
        }

        [HttpGet]
        public Result<Content> SelectContentById(string id)
        {
            Result<Content> res = new Result<Content>();
            List<Content> contents = new List<Content>();
            string sql = "select * from content where id=@id";
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id),
            };
            //2、调用DataSet通用的查询的方法
            //DataSet ds=DBHelper.GetDataSet(sql,null);
            DataSet ds = DBHelper.GetDataSet(sql, parameters);

            //4、获得DataSet对象中指定的表（学生表）
            DataTable dt = ds.Tables[0];

            //5、遍历表中的每一行，把每行中的各个字段的值依次存入学生对象，添加学生对象到学生列表
            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            res.Data = contents[0];
            return res;
        }

        [HttpGet]
        public Result<List<Content>> SelectAllContent()
        {
            Result<List<Content>> res = new Result<List<Content>>();
            List<Content> contents = new List<Content>();
            string sql = "select * from content";
            
            //2、调用DataSet通用的查询的方法
            //DataSet ds=DBHelper.GetDataSet(sql,null);
            DataSet ds = DBHelper.GetDataSet(sql, null);

            //4、获得DataSet对象中指定的表（学生表）
            DataTable dt = ds.Tables[0];

            //5、遍历表中的每一行，把每行中的各个字段的值依次存入学生对象，添加学生对象到学生列表
            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            res.Data = contents;
            return res;
        }

        [HttpGet]
        public Result<List<Content>> SelectBlogRandom()
        {
            
            Result<List<Content>> res = new Result<List<Content>>();
            List<Content> contents = new List<Content>();
            string sql = "select top 5 * from content order by newid()";
            
            //2、调用DataSet通用的查询的方法
            //DataSet ds=DBHelper.GetDataSet(sql,null);
            DataSet ds = DBHelper.GetDataSet(sql, null);

            //4、获得DataSet对象中指定的表（学生表）
            DataTable dt = ds.Tables[0];

            //5、遍历表中的每一行，把每行中的各个字段的值依次存入学生对象，添加学生对象到学生列表
            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            res.Data = contents;
            return res;
        }

        [HttpGet]
        public Result<Page> Page()
        {
            Result<Page> res = new Result<Page>();
            Page page= new Page();            
            int total = 0;
            total = CountContent();
            page.Total = total;
            page.Contents = SelectAllContent().Data;
            res.Data = page;
            res.Code= 200;
            res.Message = "success";
            return res;
        }

        [HttpGet]
        public int CountContentByUserId(int userId)
        {
            string sql = "select count(*) from content where userId=@userId";
            SqlParameter[] parameters =
            {
                new SqlParameter("@userId",userId),
            };
            return (int)DBHelper.ExecuteScalar(sql, parameters);
        }

        [HttpGet]
        public int CountContent()
        {
            string sql = "select count(*) from content";
            return (int)DBHelper.ExecuteScalar(sql);
        }

        [HttpGet]
        public int CountContentByKeyword(string keyword)
        {
            string sql = "select count(*) from content where contentMd like @keyword";
            SqlParameter[] parameters =
            {
                new SqlParameter("@keyword","%"+keyword+"%"),
            };
            return (int)DBHelper.ExecuteScalar(sql,parameters);
        }
        [HttpGet]
        public Result<List<Content>> selectByCommentCount()//comment量从大到小排序
        {            
            Result<List<Content>> res = new Result<List<Content>>();
            List<Content> contents = new List<Content>();
            string sql = "select * from content order by commentCount desc";

            DataSet ds = DBHelper.GetDataSet(sql, null);

            DataTable dt = ds.Tables[0];

            Content content = null;
            foreach (DataRow dr in dt.Rows)
            {
                content = new Content();
                content.Id = Convert.ToInt32(dr["id"]);
                content.UserId = Convert.ToInt32(dr["userId"]);
                content.Title = dr["title"].ToString();
                content.ContentMd = dr["contentMd"].ToString();
                content.ContentHtml = dr["contentHtml"].ToString();
                content.Abs = dr["abs"].ToString();
                content.Cover = dr["cover"].ToString();
                content.CreateTime = Convert.ToDateTime(dr["createTime"]);
                contents.Add(content);
            }
            res.Code = 200;
            res.Message = "查询成功";
            res.Data = contents;
            return res;
        }


    }
}
